package dbLayer;

import modelLayer.Topic;

import java.sql.*;
import java.util.ArrayList;

public class TopicDb {


private Connection con;

public TopicDb() {
	con = DbConnection.getInstance().getDBcon();
}

//*********Methods***********\\
/*
/**
 * get one PartSalesOrder having the id
 * @param partorderid
 * @return singlewhere
 */
public Topic findTopic(int tNo)
{   String wClause = "  tno = '" + tNo + "'";
    return singleWhere(wClause);
}

public Topic findTopic(String topic)
{   String wClause = "  topic = '" + topic + "'";
    return singleWhere(wClause);
}

public ArrayList<Topic> findAllTopics(){
	String wClause = " ";
	return miscWhere(wClause);
}

public ArrayList<Topic> findTopicByLNo(int lNo){
	String wClause = " lno = '" + lNo + "'";
	return miscWhere(wClause);
}

/**
 * singlewhere is used when only one Topic object is to be build
 * @param wClause
 * @return TopicObj
 */
private Topic singleWhere(String wClause)
{	
    ResultSet results;
    Topic subObj = new Topic();
    String query =  buildQuery(wClause);
    System.out.println("TopicDb -singelWhere " + query); 
    try{ 
    	Statement stmt = con.createStatement();
    	stmt.setQueryTimeout(5);
    	results = stmt.executeQuery(query);
    		if( results.next() ){
    			subObj = buildTopic(results);
           
    		}//end if
    		stmt.close();
    }//end try	
    catch(Exception e){
    	System.out.println("Query exception - select Topic : "+e);
    	e.printStackTrace();
    }
    return subObj;
}

private ArrayList<Topic> miscWhere(String wClause){
	ResultSet results;
	ArrayList<Topic> list = new ArrayList<Topic>();
	
	String query = buildQuery(wClause);
	try{
		Statement stmt = con.createStatement();
	 	stmt.setQueryTimeout(5);
	 	results = stmt.executeQuery(query);
	 	while(
	 		results.next()){
	 			Topic t = new Topic();
	 			t = buildTopic(results);
	 			list.add(t);
	 		}
	}
	catch(Exception e){
		System.out.println("Der er sket en fejl");
	}	
	return list;
}

private Topic buildTopic(ResultSet results) {
	 Topic topObj = new Topic();
	 try{
		 topObj.settNo(Integer.parseInt(results.getString("tno")));
		 topObj.setTopic(results.getString("topic"));
	 }
	 catch(Exception e){
			System.out.println("Der er sket en fejl");

	 }
	return topObj;
}



/**
 * method to build the query
 * @param wClause
 * @return query
 */
	private String buildQuery(String wClause)
	{
	    String query="SELECT tno, topic FROM Topic";
		
		if (wClause.length()>0)
			query=query+" WHERE "+ wClause;
			
		return query;
	}

	/**
	 * delete partorder with id
	 * @param partorderid
	 * @return rc
	 */
	/*public int delete(int partorderid)
	{
               int rc=-1;
	  
	  	String query="DELETE FROM PartSalesOrder WHERE partorderid = '" +
				partorderid + "'";
                System.out.println(query);
	  	try{ // delete from partsalesorder
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 	  	rc = stmt.executeUpdate(query);
	 	  	stmt.close();
  		}//end try	
   	        catch(Exception ex){
	 	  	System.out.println("Delete exception in partsalesorder db: "+ex);
   	        }
		return(rc);
	}*/

	/**
	 * this query will call a method in SQL that will insert into Topic table a tublet with the given attributes 
	 * @param Topic 
	 * @return rc
	 * @throws Exception
	 */
	 public int insertTopic(Topic topic, int lNo) throws Exception
	    {
	        int nextid = GetMax.getMaxId("Select max(tno) from Topic");
	        nextid = nextid + 1;
	        System.out.println("next id = " +  nextid);
	  
	       int rc = -1;
		   String query="INSERT INTO Topic(tno, topic, lno)  VALUES('"+
				nextid + "', '" +
				topic.getTopic() + "', '" + 
				lNo + " ')";
	                     

	       System.out.println("insert : " + query);
	      try{ // insert new partsalesorder
	          Statement stmt = con.createStatement();
	          stmt.setQueryTimeout(5);
	     	  rc = stmt.executeUpdate(query);
	          stmt.close();
	      }//end try
	       catch(SQLException ex){
	          System.out.println("Topic ikke oprettet");
	          throw new Exception ("Topic is not inserted correct");
	       }
	       return(rc);
	    }
	
}//ends Class

